package com.lidx.back.statistics.dao.impl;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Repository;

import com.lidx.back.statistics.dao.IMaterialDao;
import com.lidx.back.statistics.entity.MaterialPO;
import com.lidx.back.statistics.entity.MaterialUpdateRequestInfo;

@Repository
public class MaterialDaoImpl implements IMaterialDao {

	@Autowired
	private SessionFactory sessionFactory;
	
	public Session getSession(){
		return sessionFactory.getCurrentSession();
	}
	
	//@Cacheable(value="materialPO")
	public List<MaterialPO> getAllMaterialPOs(String versionType, String versionNumber) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sb = new StringBuffer("from MaterialPO where 1=1");
		if(StringUtils.isNotBlank(versionType)){
			sb.append(" and versionType = '").append(versionType).append("'");
		}
		if(StringUtils.isNotBlank(versionNumber)){
			sb.append(" and versionNumber = '").append(versionNumber).append("'");
		}
		Query query = session.createQuery(sb.toString());
		query.setCacheable(true);
		query.setCacheRegion("materialPO");
		List list = query.list();
		if(list.size()>0)
			return list;
		return null;
	}

	public List<MaterialPO> getRecommendMaterialPOs(String versionType,
			String versionNumber, String sex) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from MaterialPO where versionNumber = ? and versionType = ? and gender = ? and isRecommend = ?")
				.setString(0, versionNumber)
				.setString(1, versionType)
				.setString(2, sex)
				.setString(3, "1");
		List list = query.list();
		if(list.size()>0)
			return list;
		return null;
	}

	public void updateRequest(MaterialUpdateRequestInfo info) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("update MaterialUpdateRequestInfo set versionNum=?,versionType = ?,materialNames =? where id = 1")
				.setString(0, info.getVersionNum())
				.setString(1, info.getVersionType())
				.setString(2, info.getMaterialNames());
		query.executeUpdate();
	}

	public MaterialUpdateRequestInfo getRequestInfo() {
		Session session = sessionFactory.getCurrentSession();
		return (MaterialUpdateRequestInfo) session.get(MaterialUpdateRequestInfo.class, 1);
	}

	public void deleteMaterials(String versionType, String versionNumber) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("delete from MaterialPO where versionNumber = ? and versionType = ? ")
				.setString(0, versionNumber)
				.setString(1, versionType);
		query.executeUpdate();
	}

	public void saveMaterial(MaterialPO materialPO) {
		Session session = sessionFactory.getCurrentSession();
		materialPO.setCreateTime(new Date());
		materialPO.setUpdateTime(new Date());
		session.save(materialPO);
		session.flush();
	}

	public List<String> getMaterialNames(String versionType, String versionNumber) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("select distinct uuid from material_new where versionType = :v1"
				+ " and versionNumber = :v2")
				.setString("v1", versionType)
				.setString("v2", versionNumber);
		List list = query.list();
		return list;
	}

	public List<MaterialPO> getMaterialPOsBySex(String versionType,
			String versionNumber, String sex) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sb = new StringBuffer("from MaterialPO where 1=1");
		if(StringUtils.isNotBlank(versionType)){
			sb.append(" and versionType = '").append(versionType).append("'");
		}
		if(StringUtils.isNotBlank(versionNumber)){
			sb.append(" and versionNumber = '").append(versionNumber).append("'");
		}
		if(StringUtils.isNotBlank(sex)){
			sb.append(" and gender = '").append(sex).append("'");
		}
		Query query = session.createQuery(sb.toString());
		query.setCacheable(true);
		query.setCacheRegion("materialPO");
		List list = query.list();
		if(list.size()>0)
			return list;
		return null;
	}

	public void deleteMaterial(int id) {
		MaterialPO m = (MaterialPO) getSession().get(MaterialPO.class, id);
		getSession().delete(m);
	}

	@SuppressWarnings("deprecation")
	public void clearMaterialCacheRegion() {
		sessionFactory.evictQueries("materialPO");
		sessionFactory.evict(MaterialPO.class);
	}

	public List<MaterialPO> getMaterials(MaterialPO mp) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sb = new StringBuffer("from MaterialPO where 1=1");
		String orderBy = " order by createTime desc";
		if (null != mp.getCategoryId()) {
			sb.append(" and categoryId =").append(mp.getCategoryId() + "");
		}
		if(StringUtils.isNotBlank(mp.getVersionType())){
			sb.append(" and versionType = '").append(mp.getVersionType()).append("'");
		}
		if(StringUtils.isNotBlank(mp.getVersionNumber())){
			sb.append(" and versionNumber = '").append(mp.getVersionNumber()).append("'");
		}
		/*if(StringUtils.isNotBlank(mp.getGender())){
			sb.append(" and gender = '").append(mp.getGender()).append("'");
		}
		if(StringUtils.isNotBlank(mp.getIsRecommend())){
			sb.append(" and isRecommend = '").append(mp.getIsRecommend()).append("'");
			orderBy = " order by recommendOrder";
		}*/
		/*if(StringUtils.isNotBlank(mp.getCategory())){
			sb.append(" and category = '").append(mp.getCategory()).append("'");
			orderBy = " order by categoryOrder";
		}*/
		Query query = session.createQuery(sb.toString()+orderBy);
		query.setCacheable(true);
		query.setCacheRegion("materialPO");
		List list = query.list();
		if(list.size()>0)
			return list;
		return null;
	}

	public void updateRecommendOrder(int size,String versionType,String versionNumber) {
		getSession().createQuery("update MaterialPO set recommendOrder = recommendOrder + :order "
				+ "where versionType = :v1 and versionNumber = :v2 and isRecommend = '1'")
				.setInteger("order", size)
				.setString("v1", versionType)
				.setString("v2", versionNumber)
				.executeUpdate();
	}

	public void updateCategoryOrder(int size, String versionType,
			String versionNumber, String category) {
		getSession().createQuery("update MaterialPO set categoryOrder = categoryOrder + :num "
				+ "where versionType = :v1 and versionNumber = :v2 and category = :category")
				.setInteger("num", size)
				.setString("v1", versionType)
				.setString("v2", versionNumber)
				.setString("category", category)
				.executeUpdate();
	}

	public void saveMaterials(List<MaterialPO> ms) {
		Session session = sessionFactory.getCurrentSession();
		MaterialPO materialPO = null;
		for(int i = 0;i<ms.size();i++){
			materialPO = ms.get(i);
			materialPO.setCreateTime(new Date());
			materialPO.setUpdateTime(new Date());
			session.save(materialPO);
			if(i%20 == 0){
				session.flush();
				session.clear();
			}
		}
		
	}

	public MaterialPO getMaterialById(int id) {
		MaterialPO mPo = (MaterialPO) getSession().get(MaterialPO.class, id);
		return mPo;
	}

	public void updateMaterial(MaterialPO materialPO) {
		getSession().saveOrUpdate(materialPO);
		getSession().flush();
	}

	public void updateRecommendOrder(int i, String versionType,
			String versionNumber, int recommendOrder) {
		/*String sql = "update MaterialPO set recommendOrder = recommendOrder + :order "
				+ "where versionType = :v1 and versionNumber = :v2 and recommendOrder > :recommendOrder"
				+ " and isRecommend = '1'";
		if(i < 0){
			sql = "update MaterialPO set recommendOrder = recommendOrder - :order "
					+ " where versionType = :v1 and versionNumber = :v2 and recommendOrder > :recommendOrder"
					+ " and isRecommend = '1'";
		}*/
		String sql = "update material_new set recommendOrder = recommendOrder + :order "
				+ " where versionType = :v1 and versionNumber = :v2 and recommendOrder > :recommendOrder"
				+ " and isRecommend = '1'";
		if(i < 0){
			sql = "update material_new set recommendOrder = recommendOrder - :order "
					+ " where versionType = :v1 and versionNumber = :v2 and recommendOrder > :recommendOrder"
					+ " and isRecommend = '1'";
		}
		int abIntVal = Math.abs(i);
		getSession().createSQLQuery(sql)
				.setInteger("order", abIntVal)
				.setString("v1", versionType)
				.setString("v2", versionNumber)
				.setInteger("recommendOrder", recommendOrder)
				.executeUpdate();
	}

	public void updateCategoryOrder(int i, String versionType,
			String versionNumber, String category, int categoryOrder) {
		String sql = "update material_new set categoryOrder = categoryOrder + :order "
				+ " where versionType = :v1 and versionNumber = :v2 and categoryOrder > :categoryOrder"
				+ " and category = :category";
		if(i < 0){
			sql = "update material_new set categoryOrder = categoryOrder - :order "
					+ " where versionType = :v1 and versionNumber = :v2 and category = :category "
					+ " and categoryOrder > :categoryOrder";
		}
		int abIntVal = Math.abs(i);
		getSession().createSQLQuery(sql)
				.setInteger("order", abIntVal)
				.setString("v1", versionType)
				.setString("v2", versionNumber)
				.setString("category", category)
				.setInteger("categoryOrder", categoryOrder)
				.executeUpdate();
	}

	public List<MaterialPO> getMaterialsFrontXCategory(int size) {
		return  getSession().createQuery("from MaterialPO m where m.categoryOrder <= "+size).list();
	}

	public void deleteMaterialsByIds(String s) {
		getSession().createQuery("delete from MaterialPO where id in("+s+")").executeUpdate();
	}

	public List<String> getMaterialUuids() {
		return getSession().createQuery("select distinct uuid from MaterialPO").list();
	}

}
